package net.bwie.realtime.gongdan.mapper;

import net.bwie.realtime.gongdan.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: fh
 * @Date: 2025/6/13
 */
@Mapper
public interface GongDanMapper {
    @Select("select gender name,count(*) value from user_gender_report group by gender")
    List<SugarOne> selectBabyGenderCount(String date);
    @Select("select age_range name,count(*) data from user_age_report where age_range is not null group by age_range")
    List<SugarTwo> selectBabyAgeCount();
    @Select("select province name,count(*) value from user_gender_report group by province")
    List<SugarOne> selectProvinceAgeCount(String date);
    @Select("with a1 as (\n" +
            "    select age_range                      AS name,\n" +
            "                    if(gender = '男', count(*), 0) AS names,\n" +
            "                    if(gender = '女', count(*), 0) AS data\n" +
            "    from user_label_report\n" +
            "    where age_range is not null\n" +
            "      and gender != '未识别'\n" +
            "    group by age_range,gender\n" +
            ")select name, sum(names) names, sum(data) data from a1 group by name")
    List<SugarThree> selectAgeGenderCount(String date);
    @Select("with a1 as (\n" +
            "    select province                       AS name,\n" +
            "           if(gender = '男', count(*), 0) AS names,\n" +
            "           if(gender = '女', count(*), 0) AS data,\n" +
            "           if(gender = '未识别', count(*), 0) AS data1\n" +
            "    from user_gender_report\n" +
            "    group by province,gender\n" +
            ")select name,sum(names) names, sum(data) data, sum(data1) data1\n" +
            " from a1\n" +
            " group by name")
    List<SugarFive> selectProvinceGenderCount(String date);
}
